#include "database.hpp"
#include <iostream>
#include <QtCore/QFile>
#include <QtCore/QTextStream>
#include <QtCore/QStringList>
#include <QtSql/QSqlQuery>
#include <QtSql/QSqlRecord>
#include <QtSql/QSqlError>

Database::~Database() {
    db->close();
    lock->unlock();
    delete lock;
    delete db;
    lock=0;
    db=0;
}
Database::Database(QString f) : file(f) {
    init();
    lock=new QtLockedFile(f);
    lock->lock(QtLockedFile::WriteLock);
}

void Database::init() {
    QSqlDatabase sqlite=QSqlDatabase::addDatabase("QSQLITE");
    db=&sqlite;
    db->setDatabaseName(file);
    if(!db->open()) std::cerr << "Cannot open database.\n";//error
    querier=QSqlQuery(*db);
    install();
}
///TODO: insert & updates
void Database::set(AudioFile *af) {
    /*QSqlQuery req(db);
    QSqlRecord rec;
    unsigned int idtrack=0;
    //album= addAlbum (af->getAlbum());
    //artist=addArtist(af->getArtist());
    //disc=addDisc(af->getCurrentCD(), album);
    req.exec("SELECT idtrack FROM track WHERE path||filename=:path LIMIT 1;"); //check if file exists in the DB
    req.bindValue(":path", af->getPath());
    req.exec();
    if(req.next()==false && af->getIdTrack()==0) { //insert new file
        req.prepare("INSERT INTO track (title, artist, album, year, currenttrack,totaltrack, currentCD,totalCD, duration, filename, path, bitrate, frequency, keywords) VALUES (:title, :artist, :album, :year, :track,:ttrack, :ccd,:tcd, :duration, :file, :dir, :br, :f, :kw);");
        req.bindValue(":artist",    af->getArtist());
        req.bindValue(":title",     af->getTitle());
        req.bindValue(":album",     af->getAlbum());
        req.bindValue(":year",      af->getYear());
        req.bindValue(":track",     af->getCurrentTrack());
        req.bindValue(":ttrack",    af->getTotalTrack());
        req.bindValue(":ccd",       af->getCurrentCD());
        req.bindValue(":tcd",       af->getTotalCD());
        req.bindValue(":duration",  af->getLength());
        req.bindValue(":file",      af->getFilename());
        req.bindValue(":dir",       af->getDirectory());
        req.bindValue(":br",        af->getBitrate());
        req.bindValue(":f",         af->getFrequency());
        ///TODO:generating keywords
        QString str=af->getArtist()+" "+af->getAlbum()+" "+af->getTitle()+" "+af->getFilename();
        str.chop(4);
        req.bindValue(":kw",        SpecialCharsRemover().removeSpecialChars(str));
        req.exec();
    } else { //update file
        rec=req.record();
        idtrack=req.value(rec.indexOf("idtrack")).toUInt();
        req.prepare("UPDATE track title=:title, currentTrack=:track, filename=:path LIMIT 1;");
        req.bindValue(":year", af->getYear());
        req.bindValue(":path", af->getPath());
        req.bindValue(":title", af->getTitle());
        req.exec();
    }/ *
    for(std::set<std::string>::iterator it=af->getGenres().begin(); it!=af->getGenres().end(); it++) { //check and add genre if do not exists
        unsigned short idgenre=0;
        if(!(*it).empty()) {
            req.prepare("SELECT idgenre FROM genre WHERE name=:genre LIMIT 1;");
            req.bindValue(":genre", (*it));
            req.exec();
            if(req.next()!=false) {
                rec=req.record();
                idgenre=req.value(rec.indexOf("idgenre")).toUInt();
            } else {
                req.prepare("INSERT INTO genre(name) VALUES(:genre);");
                req.bindValue(":genre", (*it));
                req.exec();
                req.prepare("SELECT idgenre FROM genre WHERE name=:genre LIMIT 1;");
                req.bindValue(":genre", (*it));
                req.exec();
                idgenre=req.value(rec.indexOf("idgenre")).toUInt();
            }
            req.prepare("INSERT INTO genre_track VALUES(:idtrack, :idgenre);");
            req.bindValue(":idtrack", idtrack);
            req.bindValue(":idgenre", idgenre);
            req.exec();
        }
    }*/
}

void Database::install() {
    //install structure
    QFile f("structure.sql");
    f.open(QIODevice::ReadOnly | QIODevice::Text);
    QTextStream ts(&f);
    QString lines=ts.readAll();
    f.close();
    QStringList l=lines.split(";\n\n");
    db->transaction();
    for(int i=0; i<l.size(); i++)
        if(!querier.exec(l.at(i))) {
            db->rollback();
            std::cerr<<"Database error: "<<querier.lastError().text().toLocal8Bit().constData()<<" in "<<querier.lastQuery().toLocal8Bit().constData()<<"\n";
            return;
        }
    db->commit();
    //add some data (genre, tags, ...)
    //RessourceListReader *rlr=new RessourceListReader();
    db->transaction();
    querier.exec("INSERT INTO taggroup(name) VALUES ('Genre')");
    //wxLongLong idparent=query->GetLastRowId();

    querier.exec("INSERT INTO taggroup(name) VALUES ('Dance')");
    querier.exec("INSERT INTO taggroup(name) VALUES ('Ambiance')");
    querier.exec("INSERT INTO taggroup(name) VALUES ('Instrument')");
    querier.exec("INSERT INTO taggroup(name) VALUES ('Language')");
    querier.exec("INSERT INTO taggroup(name) VALUES ('Country')");
    db->commit();
    //delete rlr;
    /*} catch() {
        std::cerr << e.GetErrorCode() << ":" << (const char*)(e.GetMessage().mb_str()) << std::endl;
        db->rollback();
    }*/
}

QSqlDatabase * Database::getDatabase() {return db;}

void Database::deleteTrack(AudioFile *af) {
    querier.prepare("DELETE FROM track WHERE id=:id LIMIT 1");
    querier.bindValue(":id", af->getId());
    querier.exec();
    cleanup();
}
unsigned int Database::addTrack(AudioFile *af) {
    unsigned int album=addAlbum(af->getAlbum());
    unsigned int artist=addArtist(af->getArtist());
    if(af->getId()==0) { //add
        querier.prepare("INSERT INTO track \
                        (title, composer, currentCD, totalCD, year, filepath, filename, filesize, currenttrack, totaltrack, duration, bitrate, samplerate, genre, album, artist)\
                        VALUES(:t, :c, :cdn, :cdx, :y, :fn, :fp, :fs, :ct, :tt, :d, :b, :sr, :g, :al, :ar));");
        querier.bindValue(":t",  af->getTitle());
        querier.bindValue(":c",  af->getComposer());
        querier.bindValue(":cdn",af->getDiscNumber());
        querier.bindValue(":cdx",af->getDiscCount());
        querier.bindValue(":y",  af->getYear());
        querier.bindValue(":fn", af->getFilename());
        querier.bindValue(":fp", af->getFiledir());
        querier.bindValue(":fs", af->getFilesize());
        querier.bindValue(":ct", af->getTrackNumber());
        querier.bindValue(":tt", af->getTrackCount());
        querier.bindValue(":d",  af->getTime());
        querier.bindValue(":b",  af->getBitrate());
        querier.bindValue(":sr", af->getSamplerate());
        querier.bindValue(":g",  af->getGenre());
        querier.bindValue(":al", album);
        querier.bindValue(":ar", artist);
        querier.exec();
        af->setId(querier.lastInsertId().toUInt());
    } else { //update
        querier.prepare("UPDATE track SET title=:t, composer=:c, currentCD=:cdn, totalCD=:cdx, filesize=:fs, currenttrack=:ct, totaltrack=:tt genre=:g, album=:al, artist=:ar, year=:y WHEREid=:id LIMIT;");
        querier.bindValue(":t",  af->getTitle());
        querier.bindValue(":c",  af->getComposer());
        querier.bindValue(":cdn",af->getDiscNumber());
        querier.bindValue(":cdx",af->getDiscCount());
        querier.bindValue(":y",  af->getYear());
        querier.bindValue(":fs", af->getFilesize());
        querier.bindValue(":ct", af->getTrackNumber());
        querier.bindValue(":tt", af->getTrackCount());
        querier.bindValue(":d",  af->getTime());
        querier.bindValue(":g",  af->getGenre());
        querier.bindValue(":al", album);
        querier.bindValue(":ar", artist);
        querier.exec();
        querier.bindValue(":id", af->getId());
        querier.exec();
    }
    //add genre
    return af->getId();
}
unsigned int Database::addAlbum(QString name, unsigned int id, unsigned short cds, bool compilation) {
    querier.prepare("SELECT idalbum FROM album WHERE name LIKE :album;");
    querier.bindValue(":album", name);
    querier.exec();
    if(querier.next() || id>0) {
        querier.prepare("UPDATE album SET name=:name, cdcount=:cds, compilation=:compil WHERE idalbum=:id LIMIT 1");
        querier.bindValue(":id",     id>0?id:querier.value(0));
        querier.bindValue(":name",   name);
        querier.bindValue(":cds",    cds);
        querier.bindValue(":compil", compilation);
        querier.exec();
        return querier.value(0).toUInt();
    } else {
        querier.prepare("INSERT INTO album(name,cdcount,compilation) VALUES(:name, :cds, :compil)");
        querier.bindValue(":name",   name);
        querier.bindValue(":cds",    cds);
        querier.bindValue(":compil", compilation);
        querier.exec();
        return querier.lastInsertId().toUInt();
    }
}
unsigned int Database::addArtist(QString name, unsigned int id) {
    querier.prepare("SELECT idartist FROM artist WHERE name LIKE :artist;");
    querier.bindValue(":artist", name);
    querier.exec();
    if(querier.next() || id>0) {
        querier.prepare("UPDATE artist SET name=:name WHERE idartist=:id LIMIT 1");
        querier.bindValue(":id",   id>0?id:querier.value(0));
        querier.bindValue(":name", name);
        querier.exec();
        return querier.value(0).toUInt();
    } else {
        querier.prepare("INSERT INTO artist(name) VALUES(:name)");
        querier.bindValue(":name",   name);
        querier.exec();
        return querier.lastInsertId().toUInt();
    }
}

void Database::reset() {
    db->transaction();
    querier.exec("DELETE FROM taggedtrack");
    querier.exec("DELETE FROM track");
    querier.exec("DELETE FROM artist");
    querier.exec("DELETE FROM album");
    querier.exec("DELETE FROM tag");
    querier.exec("DELETE FROM taggroup");
    querier.exec("DELETE FROM device");
    db->commit();
}
void Database::cleanup() {
    db->transaction();
    querier.exec("DELETE FROM artist WHERE idartist NOT IN (SELECT DISTINCT artist FROM track)");
    querier.exec("DELETE FROM album  WHERE idalbum  NOT IN (SELECT DISTINCT album  FROM track)");
    db->commit();
}

void Database::run() {
    emit complete();
}

void Database::played(unsigned int id) {
    querier.prepare("INSERT INTO history VALUES(:id);");
    querier.bindValue(":id", id);
    querier.exec();
}
